﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Runtime.Serialization.Formatters;
using System.Runtime.Serialization.Formatters.Soap;
using System.ServiceModel;
using System.Text;
using System.Xml;
using System.Xml.Serialization;
using PmuRegistry.Data.BusinessObjects;
using PmuRegistry.Data.Entities;
using TVA.PhasorProtocols;

namespace PmuRegistry.Data
{
	public static class CommonFunctions
	{
		public static string GetReturnMessage(string source, string userMessage, string systemMessage, string detail, MessageType userMessageType)
		{
			Message message = new Message();
			message.Source = source;
			message.UserMessage = userMessage;
			message.SystemMessage = systemMessage;
			message.Detail = detail;
			message.UserMessageType = userMessageType;

			MemoryStream memoryStream = new MemoryStream();
			XmlSerializer xs = new XmlSerializer(typeof(Message));
			XmlTextWriter xmlTextWriter = new XmlTextWriter(memoryStream, Encoding.UTF8);
			xs.Serialize(xmlTextWriter, message);
			memoryStream = (MemoryStream)xmlTextWriter.BaseStream;
			return (new UTF8Encoding()).GetString(memoryStream.ToArray());
		}

		static void LogException(string source, Exception ex)
		{
			DataConnection connection = new DataConnection();
			try
			{
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Insert Into ErrorLog (Source, Message, Detail) Values (@source, @message, @detail)";
				command.Parameters.Add(AddWithValue(command, "@source", source));
				command.Parameters.Add(AddWithValue(command, "@message", ex.Message));
				command.Parameters.Add(AddWithValue(command, "@detail", ex.ToString()));
				command.ExecuteNonQuery();
			}
			catch
			{
				//Do nothing. Dont worry about it.
			}
			finally
			{
				connection.Dispose();
			}
		}

		public static IDbDataParameter AddWithValue(IDbCommand command, string name, object value)
		{
			return AddWithValue(command, name, value, ParameterDirection.Input);
		}

		public static IDbDataParameter AddWithValue(IDbCommand command, string name, object value, ParameterDirection direction)
		{
			IDbDataParameter param = command.CreateParameter();
			param.ParameterName = name;
			param.Value = value;
			param.Direction = direction;
			return param;
		}

		public static Dictionary<string, string> GetTimeZones(bool isOptional)
		{
			try
			{
				Dictionary<string, string> timeZonesList = new Dictionary<string, string>();
				if (isOptional)
					timeZonesList.Add("", "Select Timezone");

				foreach (TimeZoneInfo tzi in TimeZoneInfo.GetSystemTimeZones())
				{
					if (!timeZonesList.ContainsKey(tzi.StandardName))
						timeZonesList.Add(tzi.StandardName, tzi.DisplayName);
				}
				return timeZonesList;
			}
			catch (Exception ex)
			{
				LogException("GetTimeZones", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Get Timezones List", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
		}

		public static string GetExecutingAssemblyPath()
		{
			try
			{
				return TVA.IO.FilePath.GetAbsolutePath("Temp");
			}
			catch (Exception ex)
			{
				LogException("GetExecutingAssemblyPath", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Current Execution Path", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);				
			}
		}

		public static string SaveIniFile(Stream input)
		{
			try
			{
				string fileName = Guid.NewGuid().ToString() + ".ini";
				using (FileStream fs = File.Create(GetExecutingAssemblyPath() + "\\" + fileName))
				{
					byte[] buffer = new byte[4096];
					int bytesRead;
					while ((bytesRead = input.Read(buffer, 0, buffer.Length)) != 0)
					{
						fs.Write(buffer, 0, bytesRead);
					}
				}				
				return fileName;
			}
			catch (Exception ex)
			{
				LogException("SaveIniFile", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Upload INI File", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
		}

		public static List<WizardDeviceInfo> GetWizardConfigurationInfo(Stream inputStream)
		{
			List<WizardDeviceInfo> wizardDeviceInfoList = new List<WizardDeviceInfo>();
			try
			{
				SoapFormatter sf = new SoapFormatter();
				sf.AssemblyFormat = FormatterAssemblyStyle.Simple;
				sf.TypeFormat = FormatterTypeStyle.TypesWhenNeeded;
				IConfigurationFrame configurationFrame = sf.Deserialize(inputStream) as IConfigurationFrame;
				if (configurationFrame != null)
				{
					int parentAccessID = configurationFrame.IDCode;
					wizardDeviceInfoList = (from cell in configurationFrame.Cells
											select new WizardDeviceInfo()
											{
												Acronym = cell.StationName.Replace(" ", "").ToUpper(),
												Name = CultureInfo.CurrentUICulture.TextInfo.ToTitleCase(cell.StationName.ToLower()),
                                                Longitude = -98.6m,
												Latitude = 37.5m,
												VendorDeviceID = (int?)null,
												StatusID = 1,
												AccessID = cell.IDCode,
												ParentAccessID = parentAccessID,
												Include = true,
												DigitalCount = cell.DigitalDefinitions.Count(),
												AnalogCount = cell.AnalogDefinitions.Count(),
												AddDigitals = false,
												AddAnalogs = false,
												PhasorList = (from phasor in cell.PhasorDefinitions
															  select new PhasorInfo()
															  {
																  Label = CultureInfo.CurrentUICulture.TextInfo.ToTitleCase(phasor.Label.Replace("?", " ").Trim().ToLower()),
																  Type = phasor.PhasorType == PhasorType.Current ? "I" : "V",
																  Phase = "+",
																  DestinationLabel = "",
																  Include = true
															  }).ToList()
											}).ToList();

				}
				return wizardDeviceInfoList;
			}
			catch(Exception ex)
			{
				LogException("GetWizardConfigurationInfo", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Parse Configuration File", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}			
		}

		public static string SaveWizardConfigurationInfo(List<WizardDeviceInfo> wizardDeviceInfoList, int protocolID, int companyID, int interconnectionID, int? parentID)
		{
			try
			{
				//Before we start saving information into database make sure all the device acronyms are unique in the collection.
				//List<string> acronymList = new List<string>();
				//acronymList = (from item in wizardDeviceInfoList
				//               select item.Acronym).Distinct().ToList();

				//if (acronymList.Count != wizardDeviceInfoList.Count)	//i.e. there are duplicate acronyms.
				//    throw new ArgumentException("Duplicate Acronyms Exists!");

				List<string> nondistinctAcronymList = new List<string>();
				nondistinctAcronymList = (from item in wizardDeviceInfoList
										  where item.Include == true
										  group item by item.Acronym into grouped
										  where grouped.Count() > 1
										  select grouped.Key).ToList();

				if (nondistinctAcronymList.Count > 0)
				{
					StringBuilder sb = new StringBuilder("Duplicate Acronyms Exist");
					foreach (string item in nondistinctAcronymList)
					{
						sb.AppendLine();
						sb.Append(item);
					}
					throw new ArgumentException(sb.ToString());
				}

				int loadOrder = 1;
				foreach (WizardDeviceInfo info in wizardDeviceInfoList)
				{
					if (info.Include)
					{
						Device device = new Device();						
						device.Acronym = info.Acronym;
						device.Name = info.Name;
						device.SubstationAcronym = string.Empty;
						device.SubstationName = string.Empty;
						device.Longitude = info.Longitude;
						device.Latitude = info.Latitude;
						device.TimeZone = string.Empty;
						device.InterconnectionID = interconnectionID;
						device.CompanyID = companyID;
						device.VendorDeviceID = info.VendorDeviceID == null ? (int?)null : info.VendorDeviceID == 0 ? (int?)null : info.VendorDeviceID;
						device.ProtocolID = protocolID;
						device.MeasuredLines = 1;
						device.IsConcentrator = false;
						device.ParentID = parentID;
						device.StatusID = info.StatusID;
						device.StatusDate = DateTime.Now;
						device.TechnicalContactID = (int?)null;
						device.InServiceDate = (DateTime?)null;
                        device.FundingSourceID = (int?)null;

						//If Add Digitals and Add Analogs is checked for the device then, if digitals and analogs are available i.e. count>0 then add them as measurements.		
						int digitalCount = 0;
						if (info.AddDigitals && info.DigitalCount > 0)
						{
							digitalCount = info.DigitalCount;
						}
						int analogCount = 0;
						if (info.AddAnalogs && info.AnalogCount > 0)
						{
							analogCount = info.AnalogCount;
						}

						Device existingDevice = GetDeviceByAcronym(info.Acronym);
						if (existingDevice != null)
						{
							device.ID = existingDevice.ID;
							device.TimeZone = existingDevice.TimeZone;
							device.MeasuredLines = existingDevice.MeasuredLines;
							SaveDevice(device, false, digitalCount, analogCount);
						}
						else
							SaveDevice(device, true, digitalCount, analogCount);

						Device addedDevice = GetDeviceByAcronym(info.Acronym);
						int count = 1;
						foreach (PhasorInfo phasorInfo in info.PhasorList)
						{
							if (phasorInfo.Label.ToLower() != "unused")
							{
								Phasor phasor = new Phasor();
								phasor.DeviceID = addedDevice.ID;
								phasor.Label = phasorInfo.Label;
								phasor.Type = phasorInfo.Type;
								phasor.Phase = phasorInfo.Phase;
								phasor.DestinationPhasorID = null;
								phasor.SourceIndex = count;

								Phasor existingPhasor = GetPhasorBySourceIndex(addedDevice.ID, phasor.SourceIndex);
								if (existingPhasor != null)
								{
									phasor.ID = existingPhasor.ID;
									SavePhasor(phasor, false);
								}
								else
									SavePhasor(phasor, true);
							}
							count++;
						}
					}
					loadOrder++;
				}
				return "Configuration Information Saved Successfully";
			}
			catch (Exception ex)
			{
				LogException("SaveWizardConfigurationInfo", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Save Configuration Information", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);	
			}
		}

		public static Dictionary<int, string> GetDeviceStatuses()
		{			
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<int, string> deviceStatuses = new Dictionary<int, string>();
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "SELECT ID, Name FROM DeviceStatus Order By Name";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!deviceStatuses.ContainsKey(Convert.ToInt32(row["ID"].ToString())))
						deviceStatuses.Add(Convert.ToInt32(row["ID"].ToString()), row["Name"].ToString());
				}
				return deviceStatuses;
			}
			catch (Exception ex)
			{
				LogException("GetDeviceStatuses", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Device Status Information", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();	
			}			
		}

		public static Dictionary<int, string> GetContactTypes()
		{			
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<int, string> contactTypes = new Dictionary<int, string>();
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "SELECT ID, Name FROM ContactType Order By Name";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!contactTypes.ContainsKey(Convert.ToInt32(row["ID"].ToString())))
						contactTypes.Add(Convert.ToInt32(row["ID"].ToString()), row["Name"].ToString());
				}
				return contactTypes;
			}
			catch (Exception ex)
			{
				LogException("GetContactTypes", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Contact Types", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}			
		}

		public static bool UserIsValid(string userName, string password)
		{
			DataConnection connection = new DataConnection();			
			try
			{
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select Count(*) From [User] Where LOWER(UserName) = @userName AND Password = @password";
				command.Parameters.Add(AddWithValue(command, "@userName", userName));
				command.Parameters.Add(AddWithValue(command, "@password", password));

				return (int)command.ExecuteScalar() > 0;
			}
			catch (Exception ex)
			{
				LogException("UserIsValid", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Authenticate User", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}			
		}

		#region " Manage Device Code"

		public static List<Device> GetDeviceList()
		{			
			DataConnection connection = new DataConnection();
			try
			{
				List<Device> deviceList = new List<Device>();
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select * From DeviceDetail Order By Acronym";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());
				deviceList = (from item in resultTable.AsEnumerable()
							  select new Device()
							  {
								  ID = item.Field<int>("ID"),
								  Acronym = item.Field<string>("Acronym"),
								  Name = item.Field<string>("Name"),
								  SubstationAcronym = item.Field<string>("SubstationAcronym"),
								  SubstationName = item.Field<string>("SubstationName"),
								  Longitude = item.Field<decimal?>("Longitude"),
								  Latitude = item.Field<decimal?>("Latitude"),
								  TimeZone = item.Field<string>("TimeZone"),
								  InterconnectionID = item.Field<int>("InterconnectionID"),
								  CompanyID = item.Field<int>("CompanyID"),
								  VendorDeviceID = item.Field<int?>("VendorDeviceID"),
								  ProtocolID = item.Field<int?>("ProtocolID"),
								  MeasuredLines = item.Field<int>("MeasuredLines"),
								  IsConcentrator = Convert.ToBoolean(item.Field<object>("IsConcentrator")),
								  ParentID = item.Field<int?>("ParentID"),
								  StatusID = item.Field<int>("StatusID"),
								  StatusDate = item.Field<DateTime>("StatusDate"),
								  TechnicalContactID = item.Field<int?>("TechnicalContactID"),
								  InServiceDate = item.Field<DateTime?>("InServiceDate"),
                                  FundingSourceID = item.Field<int?>("FundingSourceID"),
								  CreatedOn = item.Field<DateTime>("CreatedOn"),
								  UpdatedOn = item.Field<DateTime>("UpdatedOn"),
								  CompanyName = item.Field<string>("CompanyName"),
								  CompanyAcronym = item.Field<string>("CompanyAcronym"),
								  VendorDeviceName = item.Field<string>("VendorDeviceName"),
								  VendorAcronym = item.Field<string>("VendorAcronym"),
								  ProtocolName = item.Field<string>("ProtocolName"),
								  InterconnectionName = item.Field<string>("InterconnectionName"),
								  ParentAcronym = item.Field<string>("ParentAcronym"),
								  DeviceStatusName = item.Field<string>("StatusName"),
								  ContactFullName = item.Field<string>("ContactFullName"),
								  CompanyMapAcronym = item.Field<string>("CompanyMapAcronym"),
                                  FundingSourceName = item.Field<string>("FundingSourceName")
							  }).ToList();
				return deviceList;
			}
			catch (Exception ex)
			{
				LogException("GetDeviceList", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Devices Information", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}
		}

		public static List<Device> GetDeviceListByParentID(int parentID)
		{
			List<Device> deviceList = new List<Device>();
			deviceList = (from item in GetDeviceList()
						  where item.ParentID == parentID
						  select item).ToList();
			return deviceList;
		}

		public static string SaveDevice(Device device, bool isNew, int digitalCount, int analogCount)
		{
			DataConnection connection = new DataConnection();
			try
			{
				IDbCommand command = connection.Connection.CreateCommand();

				if (isNew)
					command.CommandText = "Insert Into Device (Acronym, Name, SubstationAcronym, SubstationName, Longitude, Latitude, TimeZone, InterconnectionID, CompanyID, VendorDeviceID, " +
						"ProtocolID, MeasuredLines, IsConcentrator, ParentID, StatusID, StatusDate, TechnicalContactID, InServiceDate, FundingSourceID) Values (@acronym, @name, @substationAcronym, @substationName, " +
						"@longitude, @latitude, @timeZone, @interconnectionID, @companyID, @vendorDeviceID, @protocolID, @measuredLines, @isConcentrator, @parentID, @statusID, @statusDate, " +
						"@technicalContactID, @inServiceDate, @fundingSourceID)";
				else
					command.CommandText = "Update Device Set Acronym = @acronym, Name = @name, SubstationAcronym = @substationAcronym, SubstationName = @substationName, Longitude = @longitude, " +
						"Latitude = @latitude, TimeZone = @timezone, InterconnectionID = @interconnectionID, CompanyID = @companyID, VendorDeviceID = @vendorDeviceID, ProtocolID = @protocolID, " +
						"MeasuredLines = @measuredLines, IsConcentrator = @isConcentrator, ParentID = @parentID, StatusID = @statusID, StatusDate = @statusDate, TechnicalContactID = @technicalContactID, " +
						"InServiceDate = @inServiceDate, FundingSourceID = @fundingSourceID, UpdatedOn = @updatedOn WHERE ID = @id";

				command.CommandType = CommandType.Text;
				command.Parameters.Add(AddWithValue(command, "@acronym", device.Acronym));
				command.Parameters.Add(AddWithValue(command, "@name", device.Name));
				command.Parameters.Add(AddWithValue(command, "@substationAcronym", device.SubstationAcronym));
				command.Parameters.Add(AddWithValue(command, "@substationName", device.SubstationName));
                command.Parameters.Add(AddWithValue(command, "@longitude", device.Longitude ?? -98.6m));
				command.Parameters.Add(AddWithValue(command, "@latitude", device.Latitude ?? 37.5m));
				command.Parameters.Add(AddWithValue(command, "@timezone", device.TimeZone));
				command.Parameters.Add(AddWithValue(command, "@interconnectionID", device.InterconnectionID));
				command.Parameters.Add(AddWithValue(command, "@companyID", device.CompanyID));
				command.Parameters.Add(AddWithValue(command, "@vendorDeviceID", device.VendorDeviceID == null ? (object)DBNull.Value : device.VendorDeviceID == 0 ? (object)DBNull.Value : device.VendorDeviceID));
				command.Parameters.Add(AddWithValue(command, "@protocolID", device.ProtocolID ?? (object)DBNull.Value));
				command.Parameters.Add(AddWithValue(command, "@measuredLines", device.MeasuredLines));
				command.Parameters.Add(AddWithValue(command, "@isConcentrator", device.IsConcentrator));
				command.Parameters.Add(AddWithValue(command, "@parentID", device.ParentID ?? (object)DBNull.Value));
				command.Parameters.Add(AddWithValue(command, "@statusID", device.StatusID));
				command.Parameters.Add(AddWithValue(command, "@statusDate", device.StatusDate));
				command.Parameters.Add(AddWithValue(command, "@technicalContactID", device.TechnicalContactID ?? (object)DBNull.Value));
				command.Parameters.Add(AddWithValue(command, "@inServiceDate", device.InServiceDate ?? (object)DBNull.Value));
                command.Parameters.Add(AddWithValue(command, "@fundingSourceID", device.FundingSourceID ?? (object)DBNull.Value));

				if (!isNew)
				{
					command.Parameters.Add(AddWithValue(command, "@id", device.ID));
					command.Parameters.Add(AddWithValue(command, "@updatedOn", DateTime.Now));
				}

				command.ExecuteNonQuery();

				if (device.IsConcentrator)
					return "Concentrator Device Information Saved Successfully";		//As we do not add measurements for PDC device or device which is concentrator.

				Device addedDevice = new Device();
				addedDevice = GetDeviceByAcronym(device.Acronym);
				DataTable pmuSignalTypes = new DataTable();
				pmuSignalTypes = GetPmuSignalTypes();

				Measurement measurement;
				foreach (DataRow row in pmuSignalTypes.Rows)	//This will only create or update PMU related measurements and not phasor related.
				{
					measurement = new Measurement();					
					measurement.DeviceID = addedDevice.ID;
					measurement.PointTag = addedDevice.CompanyAcronym + "_" + addedDevice.Acronym + ":" + addedDevice.VendorAcronym + row["Abbreviation"].ToString();
					measurement.AlternateTag = string.Empty;
					measurement.SignalTypeID = Convert.ToInt32(row["ID"]);
					measurement.PhasorSourceIndex = (int?)null;
					measurement.SignalReference = addedDevice.Acronym + "-" + row["Suffix"].ToString();
					measurement.Adder = 0.0d;
					measurement.Multiplier = 1.0d;
					measurement.Description = addedDevice.Name + " " + addedDevice.VendorDeviceName + " " + row["Name"].ToString();
					measurement.Enabled = true;
					if (isNew)	//if it is a new device then measurements are new too. So don't worry about updating them.
						SaveMeasurement(measurement, true);
					else	//if device is existing one, then check and see if its measusremnts exist, if so then update measurements.
					{
						Measurement existingMeasurement = new Measurement();
						existingMeasurement = GetMeasurementInfo(measurement.DeviceID, row["Suffix"].ToString(), measurement.PhasorSourceIndex);

						if (existingMeasurement == null)	//measurement does not exist for this device and signal type then add as a new measurement otherwise update.
							SaveMeasurement(measurement, true);
						else
						{
							measurement.SignalID = existingMeasurement.SignalID;
							SaveMeasurement(measurement, false);
						}
					}
				}

				if (digitalCount > 0)
				{
					for (int i = 0; i < digitalCount; i++)
					{
						measurement = new Measurement();						
						measurement.DeviceID = addedDevice.ID;
						measurement.PointTag = addedDevice.CompanyAcronym + "_" + addedDevice.Acronym + ":" + addedDevice.VendorAcronym + "D" + i.ToString();
						measurement.AlternateTag = string.Empty;
						measurement.SignalTypeID = GetSignalTypeID("DV");
						measurement.PhasorSourceIndex = (int?)null;
						measurement.SignalReference = addedDevice.Acronym + "-DV" + i.ToString();
						measurement.Adder = 0.0d;
						measurement.Multiplier = 1.0d;
						measurement.Description = addedDevice.Name + " " + addedDevice.VendorDeviceName + " Digital Value " + i.ToString();
						measurement.Enabled = true;
						if (isNew)	//if it is a new device then measurements are new too. So don't worry about updating them.
							SaveMeasurement(measurement, true);
						else	//if device is existing one, then check and see if its measusremnts exist, if so then update measurements.
						{
							Measurement existingMeasurement = new Measurement();
							//we will compare using signal reference as signal suffix doesn't provide uniqueness.
							existingMeasurement = GetMeasurementInfoBySignalReference(measurement.DeviceID, measurement.SignalReference, measurement.PhasorSourceIndex);

							if (existingMeasurement == null)	//measurement does not exist for this device and signal type then add as a new measurement otherwise update.
								SaveMeasurement(measurement, true);
							else
							{
								measurement.SignalID = existingMeasurement.SignalID;
								SaveMeasurement(measurement, false);
							}
						}
					}
				}

				if (analogCount > 0)
				{
					for (int i = 0; i < analogCount; i++)
					{
						measurement = new Measurement();						
						measurement.DeviceID = addedDevice.ID;
						measurement.PointTag = addedDevice.CompanyAcronym + "_" + addedDevice.Acronym + ":" + addedDevice.VendorAcronym + "A" + i.ToString();
						measurement.AlternateTag = string.Empty;
						measurement.SignalTypeID = GetSignalTypeID("AV");
						measurement.PhasorSourceIndex = (int?)null;
						measurement.SignalReference = addedDevice.Acronym + "-AV" + i.ToString();
						measurement.Adder = 0.0d;
						measurement.Multiplier = 1.0d;
						measurement.Description = addedDevice.Name + " " + addedDevice.VendorDeviceName + " Analog Value " + i.ToString();
						measurement.Enabled = true;
						if (isNew)	//if it is a new device then measurements are new too. So don't worry about updating them.
							SaveMeasurement(measurement, true);
						else	//if device is existing one, then check and see if its measusremnts exist, if so then update measurements.
						{
							Measurement existingMeasurement = new Measurement();
							existingMeasurement = GetMeasurementInfoBySignalReference(measurement.DeviceID, measurement.SignalReference, measurement.PhasorSourceIndex);

							if (existingMeasurement == null)	//measurement does not exist for this device and signal type then add as a new measurement otherwise update.
								SaveMeasurement(measurement, true);
							else
							{
								measurement.SignalID = existingMeasurement.SignalID;
								SaveMeasurement(measurement, false);
							}
						}
					}
				}

				if (!isNew)
				{
					//After all the PMU related measurements are updated then lets go through each phasors for the PMU
					//and update all the phasors and their measurements to reflect changes made to the PMU configuration.
					//We are not going to make any changes to the Phasor definition itselft but only to reflect PMU related
					//changes in the measurement.

					foreach (Phasor phasor in GetPhasorList(addedDevice.ID))
					{
						SavePhasor(phasor, false);	//we will save phasor without modifying it so that only measurements will reflect changes related to PMU.
						//nothing will change in phasor itself.
					}
				}

				return "Device Information Saved Successfully";
			}
			catch (Exception ex)
			{
				LogException("SaveDevice", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Save Device Information", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);				
			}
			finally
			{
				connection.Dispose();
			}
		}

		public static Dictionary<int, string> GetDevices(DeviceType deviceType, bool isOptional)
		{			
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<int, string> deviceList = new Dictionary<int, string>();
				if (isOptional)
					deviceList.Add(0, "Select Device");

				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				if (deviceType == DeviceType.Concentrator)
				{
					command.CommandText = "Select ID, Acronym From Device Where IsConcentrator = @isConcentrator Order By Acronym";
					command.Parameters.Add(AddWithValue(command, "@isConcentrator", true));
				}
				else if (deviceType == DeviceType.NonConcentrator)
				{
					command.CommandText = "Select ID, Acronym From Device Where IsConcentrator = @isConcentrator Order By Acronym";
					command.Parameters.Add(AddWithValue(command, "@isConcentrator", false));
				}
				else
					command.CommandText = "Select ID, Acronym From Device Order By Acronym";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!deviceList.ContainsKey(Convert.ToInt32(row["ID"])))
						deviceList.Add(Convert.ToInt32(row["ID"]), row["Acronym"].ToString());
				}

				return deviceList;
			}
			catch (Exception ex)
			{
				LogException("GetDevices", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Devices", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}
		}

		public static Device GetDeviceByDeviceID(int deviceID)
		{
			List<Device> deviceList = new List<Device>();
			deviceList = (from item in GetDeviceList()
						  where item.ID == deviceID
						  select item).ToList();
			if (deviceList.Count > 0)
				return deviceList[0];
			else
				return null;
		}

		public static Device GetDeviceByAcronym(string acronym)
		{
			List<Device> deviceList = new List<Device>();
			deviceList = (from item in GetDeviceList()
						  where item.Acronym == acronym
						  select item).ToList();
			if (deviceList.Count > 0)
				return deviceList[0];
			else
				return null;
		}			

		public static Device GetConcentratorDevice(int deviceID)
		{
			Device device = new Device();
			device = GetDeviceByDeviceID(deviceID);
			if (device.IsConcentrator)
				return device;
			else
				return null;
		}

		public static List<Device> GetRecentDevices()
		{
			List<Device> deviceList = new List<Device>();
			deviceList = (from item in GetDeviceList()
						  orderby item.CreatedOn descending
						  select item).Take(9).ToList();
			return deviceList;
		}

		//public static Dictionary<string, DateTime> GetRecentDevices()
		//{			
		//    DataConnection connection = new DataConnection();
		//    try
		//    {
		//        Dictionary<string, DateTime> deviceList = new Dictionary<string, DateTime>();
		//        IDbCommand command = connection.Connection.CreateCommand();
		//        command.CommandType = CommandType.Text;
		//        command.CommandText = "Select Top 8 Acronym, CreatedOn From Device Order By CreatedOn DESC, Acronym";
		//        DataTable resultTable = new DataTable();
		//        resultTable.Load(command.ExecuteReader());

		//        foreach (DataRow row in resultTable.Rows)
		//        {
		//            if (!deviceList.ContainsKey(row["Acronym"].ToString()))
		//                deviceList.Add(row["Acronym"].ToString(), Convert.ToDateTime(row["CreatedOn"]));
		//        }
		//        return deviceList;
		//    }
		//    catch (Exception ex)
		//    {
		//        LogException("GetRecentDevices", ex);
		//        CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Recently Added Devices", SystemMessage = ex.Message };
		//        throw new FaultException<CustomServiceFault>(fault);
		//    }
		//    finally
		//    {
		//        connection.Dispose();
		//    }			
		//}

		public static Dictionary<string, int> GetReportingPmusStat()
		{			
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<string, int> statList = new Dictionary<string, int>();
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select StatusName, Count(*) AS PmuCount From DeviceDetail WHERE StatusName IN ('Operational', 'Out of Service', 'Validated') " +
										"Group By StatusName UNION ALL Select 'Total' AS StatusName, Count(*) AS PmuCount From DeviceDetail WHERE StatusName IN " +
										"('Operational', 'Out of Service', 'Validated')";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!statList.ContainsKey(row["StatusName"].ToString()))
						statList.Add(row["StatusName"].ToString(), Convert.ToInt32(row["PmuCount"]));
				}
				return statList;
			}
			catch (Exception ex)
			{
				LogException("GetReportingPmusStat", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Reporting PMUs Stats", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}
		}

		public static Dictionary<string, int> GetPlannedPmusStat()
		{			
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<string, int> statList = new Dictionary<string, int>();
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select StatusName, Count(*) AS PmuCount From DeviceDetail WHERE StatusName IN ('In Progress', 'Planned') " +
									"Group By StatusName UNION ALL Select 'Total' AS StatusName, Count(*) AS PmuCount From DeviceDetail WHERE StatusName IN " +
									"('In Progress', 'Planned')";


				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!statList.ContainsKey(row["StatusName"].ToString()))
						statList.Add(row["StatusName"].ToString(), Convert.ToInt32(row["PmuCount"]));
				}
				return statList;
			}
			catch (Exception ex)
			{
				LogException("GetPlannedPmusStat", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Planned PMUs Stats", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}						
		}

        public static Dictionary<int, string> GetFundingSources(bool isOptional)
        {
            DataConnection connection = new DataConnection();

            try
            {
                Dictionary<int, string> fundingSourceList = new Dictionary<int, string>();
                if (isOptional)
                    fundingSourceList.Add(0, "Select Funding Source");
                IDbCommand command = connection.Connection.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = "Select ID, SourceName From FundingSource Order By SourceName";
                DataTable resultTable = new DataTable();
                resultTable.Load(command.ExecuteReader());

                foreach (DataRow row in resultTable.Rows)
                {
                    if (!fundingSourceList.ContainsKey(Convert.ToInt32(row["ID"])))
                        fundingSourceList.Add(Convert.ToInt32(row["ID"]), row["SourceName"].ToString());
                }

                return fundingSourceList;
            }
            catch (Exception ex)
            {
                LogException("GetFundinnSources", ex);
                CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Funding Sources", SystemMessage = ex.Message };
                throw new FaultException<CustomServiceFault>(fault);
            }
            finally
            {
                connection.Dispose();
            }
        }

		#endregion

		#region " Manage Phasor Code"

		public static List<Phasor> GetPhasorList(int deviceID)
		{			
			DataConnection connection = new DataConnection();
			try
			{
				List<Phasor> phasorList = new List<Phasor>();
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select * From PhasorDetail Where DeviceID = @deviceID Order By SourceIndex";
				command.Parameters.Add(AddWithValue(command, "@deviceID", deviceID));

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				phasorList = (from item in resultTable.AsEnumerable()
							  select new Phasor()
							  {
								  ID = item.Field<int>("ID"),
								  DeviceID = item.Field<int>("DeviceID"),
								  Label = item.Field<string>("Label"),
								  Type = item.Field<string>("Type"),
								  Phase = item.Field<string>("Phase"),
								  DestinationPhasorID = item.Field<int?>("DestinationPhasorID"),
								  SourceIndex = item.Field<int>("SourceIndex"),
								  NetworkModelID = item.Field<int?>("NetworkModelID"),
								  DestinationPhasorLabel = item.Field<string>("DestinationPhasorLabel"),
								  DeviceAcronym = item.Field<string>("DeviceAcronym"),
								  PhasorType = item.Field<string>("Type") == "V" ? "Voltage" : "Current",
								  NetworkElementUID = item.Field<string>("NetworkElementUID"),
								  PhaseType = item.Field<string>("Phase") == "+" ? "Positive" : item.Field<string>("Phase") == "-" ? "Negative" :
											  item.Field<string>("Phase") == "A" ? "Phase A" : item.Field<string>("Phase") == "B" ? "Phase B" : "Phase C"
							  }).ToList();
				return phasorList;
			}
			catch (Exception ex)
			{
				LogException("GetPhasorList", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Phasor List", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}						
		}

		public static Dictionary<int, string> GetPhasors(bool isOptional)
		{
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<int, string> phasorList = new Dictionary<int, string>();
				if (isOptional)
					phasorList.Add(0, "Select Phasor");
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select ID, Label From Phasor Order By SourceIndex";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!phasorList.ContainsKey(Convert.ToInt32(row["ID"])))
						phasorList.Add(Convert.ToInt32(row["ID"]), row["Label"].ToString());
				}
				return phasorList;
			}
			catch (Exception ex)
			{
				LogException("GetPhasors", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Phasors", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}						
		}

		public static Dictionary<int, string> GetPhasorsByDevice(int deviceID, bool isOptional)
		{
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<int, string> phasorList = new Dictionary<int, string>();
				if (isOptional)
					phasorList.Add(0, "Select Phasor");
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select ID, Label From Phasor Where DeviceID = @deviceID Order By SourceIndex";
				command.Parameters.Add(AddWithValue(command, "@deviceID", deviceID));

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!phasorList.ContainsKey(Convert.ToInt32(row["ID"])))
						phasorList.Add(Convert.ToInt32(row["ID"]), row["Label"].ToString());
				}
				return phasorList;
			}
			catch (Exception ex)
			{
				LogException("GetPhasorsByDevice", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Phasors by Device", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}						
		}

		public static string SavePhasor(Phasor phasor, bool isNew)
		{
			DataConnection connection = new DataConnection();
			try
			{
				IDbCommand command = connection.Connection.CreateCommand();

				if (isNew)
					command.CommandText = "Insert Into Phasor (DeviceID, Label, Type, Phase, DestinationPhasorID, SourceIndex, NetworkModelID) Values (@deviceID, @label, @type, @phase, " +
						"@destinationPhasorID, @sourceIndex, @networkModelID)";
				else
					command.CommandText = "Update Phasor Set DeviceID =@deviceID, Label = @label, Type = @type, Phase = @phase, DestinationPhasorID = @destinationPhasorID, " +
						"SourceIndex = @sourceIndex, NetworkModelID = @networkModelID Where ID = @id";

				command.Parameters.Add(AddWithValue(command, "@deviceID", phasor.DeviceID));
				command.Parameters.Add(AddWithValue(command, "@label", phasor.Label));
				command.Parameters.Add(AddWithValue(command, "@type", phasor.Type));
				command.Parameters.Add(AddWithValue(command, "@phase", phasor.Phase));
				command.Parameters.Add(AddWithValue(command, "@destinationPhasorID", phasor.DestinationPhasorID ?? (object)DBNull.Value));
				command.Parameters.Add(AddWithValue(command, "@sourceIndex", phasor.SourceIndex));
				command.Parameters.Add(AddWithValue(command, "@networkModelID", phasor.NetworkModelID ?? (object)DBNull.Value));

				if (!isNew)
					command.Parameters.Add(AddWithValue(command, "@id", phasor.ID));

				command.ExecuteNonQuery();

				Phasor addedPhasor = new Phasor();
				//addedPhasor = GetPhasorByLabel(phasor.DeviceID, phasor.Label);
				addedPhasor = GetPhasorBySourceIndex(phasor.DeviceID, phasor.SourceIndex);

				Device device = new Device();
				device = GetDeviceByDeviceID(phasor.DeviceID);

				Measurement measurement;

				DataTable phasorSignalTypes = new DataTable();
				phasorSignalTypes = GetPhasorSignalTypes(phasor.Type);

				foreach (DataRow row in phasorSignalTypes.Rows)
				{
					measurement = new Measurement();					
					measurement.DeviceID = device.ID;
					if (addedPhasor.DestinationPhasorID.HasValue)
						measurement.PointTag = device.CompanyAcronym + "_" + device.Acronym + "-" + GetPhasorByID(addedPhasor.DeviceID, (int)addedPhasor.DestinationPhasorID).Label + ":" + device.VendorAcronym + row["Abbreviation"].ToString();
					else
						measurement.PointTag = device.CompanyAcronym + "_" + device.Acronym + "-" + row["Suffix"].ToString() + addedPhasor.SourceIndex.ToString() + ":" + device.VendorAcronym + row["Abbreviation"].ToString();
					measurement.AlternateTag = string.Empty;
					measurement.SignalTypeID = Convert.ToInt32(row["ID"]);
					measurement.PhasorSourceIndex = addedPhasor.SourceIndex;
					measurement.SignalReference = device.Acronym + "-" + row["Suffix"].ToString() + addedPhasor.SourceIndex.ToString();
					measurement.Adder = 0.0d;
					measurement.Multiplier = 1.0d;
					measurement.Description = device.Name + " " + addedPhasor.Label + " " + device.VendorDeviceName + " " + addedPhasor.PhaseType + " " + row["Name"].ToString();
					measurement.Enabled = true;
					if (isNew)	//if it is a new phasor then add measurements as new.
						SaveMeasurement(measurement, true);
					else //Check if measurement exists, if so then update them otherwise add new.
					{
						Measurement existingMeasurement = new Measurement();
						existingMeasurement = GetMeasurementInfo(measurement.DeviceID, row["Suffix"].ToString(), measurement.PhasorSourceIndex);
						if (existingMeasurement == null)
							SaveMeasurement(measurement, true);
						else
						{
							measurement.SignalID = existingMeasurement.SignalID;
							SaveMeasurement(measurement, false);
						}
					}
				}

				return "Phasor Information Saved Successfully";
			}
			catch (Exception ex)
			{
				LogException("SavePhasor", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Save Phasor Information", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);				
			}
			finally
			{
				connection.Dispose();
			}
		}

		static Phasor GetPhasorByLabel(int deviceID, string label)
		{
			List<Phasor> phasorList = new List<Phasor>();
			phasorList = (from item in GetPhasorList(deviceID)
						  where item.Label == label
						  select item).ToList();
			if (phasorList.Count > 0)
				return phasorList[0];
			else
				return null;
		}

		static Phasor GetPhasorByID(int deviceID, int phasorID)
		{
			List<Phasor> phasorList = new List<Phasor>();
			phasorList = (from item in GetPhasorList(deviceID)
						  where item.ID == phasorID
						  select item).ToList();
			if (phasorList.Count > 0)
				return phasorList[0];
			else
				return null;
		}

		static Phasor GetPhasorBySourceIndex(int deviceID, int sourceIndex)
		{
			List<Phasor> phasorList = new List<Phasor>();
			phasorList = (from item in GetPhasorList(deviceID)
						  where item.SourceIndex == sourceIndex
						  select item).ToList();
			if (phasorList.Count > 0)
				return phasorList[0];
			else
				return null;
		}

		#endregion

		#region " Manage Measurements Code"

		public static List<Measurement> GetMeasurementList()
		{			
			DataConnection connection = new DataConnection();
			try
			{
				List<Measurement> measurementList = new List<Measurement>();
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select * From MeasurementDetail Order By DeviceAcronym, PointTag, SignalReference";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				measurementList = (from item in resultTable.AsEnumerable()
								   select new Measurement()
								   {
									   SignalID = item.Field<object>("SignalID").ToString(),
									   DeviceID = item.Field<int?>("DeviceID"),
									   PointTag = item.Field<string>("PointTag"),
									   AlternateTag = item.Field<string>("AlternateTag"),
									   SignalTypeID = item.Field<int>("SignalTypeID"),
									   PhasorSourceIndex = item.Field<int?>("PhasorSourceIndex"),
									   SignalReference = item.Field<string>("SignalReference"),
									   Adder = item.Field<double>("Adder"),
									   Multiplier = item.Field<double>("Multiplier"),
									   Description = item.Field<string>("Description"),
									   Enabled = Convert.ToBoolean(item.Field<object>("Enabled")),
									   DeviceAcronym = item.Field<string>("DeviceAcronym"),
									   SignalName = item.Field<string>("SignalName"),
									   SignalAcronym = item.Field<string>("SignalAcronym"),
									   SignalSuffix = item.Field<string>("SignalTypeSuffix"),
									   PhasorLabel = item.Field<string>("PhasorLabel")
								   }).ToList();
				return measurementList;
			}
			catch (Exception ex)
			{
				LogException("GetMeasurementList", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Measurement List", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}						
		}

		public static List<Measurement> GetMeasurementsByDevice(int deviceID)
		{			
			DataConnection connection = new DataConnection();
			try
			{
				List<Measurement> measurementList = new List<Measurement>();
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select * From MeasurementDetail Where DeviceID = @deviceID Order By PointTag";
				command.Parameters.Add(AddWithValue(command, "@deviceID", deviceID));

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				measurementList = (from item in resultTable.AsEnumerable()
								   select new Measurement()
								   {
									   SignalID = item.Field<object>("SignalID").ToString(),
									   DeviceID = item.Field<int>("DeviceID"),
									   PointTag = item.Field<string>("PointTag"),
									   AlternateTag = item.Field<string>("AlternateTag"),
									   SignalTypeID = item.Field<int>("SignalTypeID"),
									   PhasorSourceIndex = item.Field<int?>("PhasorSourceIndex"),
									   SignalReference = item.Field<string>("SignalReference"),
									   Adder = item.Field<double>("Adder"),
									   Multiplier = item.Field<double>("Multiplier"),
									   Description = item.Field<string>("Description"),
									   Enabled = Convert.ToBoolean(item.Field<object>("Enabled")),
									   DeviceAcronym = item.Field<string>("DeviceAcronym"),
									   SignalName = item.Field<string>("SignalName"),
									   SignalAcronym = item.Field<string>("SignalAcronym"),
									   SignalSuffix = item.Field<string>("SignalTypeSuffix"),
									   PhasorLabel = item.Field<string>("PhasorLabel")
								   }).ToList();
				return measurementList;
			}
			catch (Exception ex)
			{
				LogException("GetMeasurementsByDevice", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Measurements for Device", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}
		}

		public static string SaveMeasurement(Measurement measurement, bool isNew)
		{
			DataConnection connection = new DataConnection();
			try
			{
				IDbCommand command = connection.Connection.CreateCommand();

				if (isNew)
					command.CommandText = "Insert Into Measurement (DeviceID, PointTag, AlternateTag, SignalTypeID, PhasorSourceIndex, SignalReference, Adder, Multiplier, Description, Enabled) " +
						"Values (@deviceID, @pointTag, @alternateTag, @signalTypeID, @phasorSourceIndex, @signalReference, @adder, @multiplier, @description, @enabled)";
				else
					command.CommandText = "Update Measurement Set DeviceID = @deviceID, PointTag = @pointTag, AlternateTag = @alternateTag, SignalTypeID = @signalTypeID, " +
						"PhasorSourceIndex = @phasorSourceIndex, SignalReference = @signalReference, Adder = @adder, Multiplier = @multiplier, Description = @description, Enabled = @enabled Where SignalID = @signalID";
								
				command.Parameters.Add(AddWithValue(command, "@deviceID", measurement.DeviceID ?? (object)DBNull.Value));
				command.Parameters.Add(AddWithValue(command, "@pointTag", measurement.PointTag));
				command.Parameters.Add(AddWithValue(command, "@alternateTag", measurement.AlternateTag));
				command.Parameters.Add(AddWithValue(command, "@signalTypeID", measurement.SignalTypeID));
				command.Parameters.Add(AddWithValue(command, "@phasorSourceIndex", measurement.PhasorSourceIndex ?? (object)DBNull.Value));
				command.Parameters.Add(AddWithValue(command, "@signalReference", measurement.SignalReference));
				command.Parameters.Add(AddWithValue(command, "@adder", measurement.Adder));
				command.Parameters.Add(AddWithValue(command, "@multiplier", measurement.Multiplier));
				command.Parameters.Add(AddWithValue(command, "@description", measurement.Description));
				command.Parameters.Add(AddWithValue(command, "@enabled", measurement.Enabled));

				if (!isNew)
				{
					if (command.Connection.ConnectionString.Contains("Microsoft.Jet.OLEDB"))
						command.Parameters.Add(AddWithValue(command, "@signalID", "{" + measurement.SignalID + "}"));
					else
						command.Parameters.Add(AddWithValue(command, "@signalID", measurement.SignalID));
				}

				command.ExecuteNonQuery();
				return "Measurement Information Saved Successfully";
			}
			catch (Exception ex)
			{
				LogException("SaveMeasurement", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Save Measurement Information", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);				
			}
			finally
			{
				connection.Dispose();
			}
		}
				
		public static Measurement GetMeasurementInfo(int? deviceID, string signalSuffix, int? phasorSourceIndex)	//we are using signal suffix because it remains same if phasor is current or voltage which helps in modifying existing measurement if phasor changes from voltage to current.
		{
			List<Measurement> measurementList = new List<Measurement>();
			measurementList = (from item in GetMeasurementsByDevice((int)deviceID)
							   where item.SignalSuffix == signalSuffix && item.PhasorSourceIndex == phasorSourceIndex
							   select item).ToList();
			if (measurementList.Count > 0)
				return measurementList[0];
			else
				return null;
		}

		private static Measurement GetMeasurementInfoBySignalReference(int? deviceID, string signalReference, int? phasorSourceIndex)
		{
			try
			{
				List<Measurement> measurementList = new List<Measurement>();
				measurementList = (from item in GetMeasurementsByDevice((int)deviceID)
								   where item.SignalReference == signalReference && item.PhasorSourceIndex == phasorSourceIndex
								   select item).ToList();
				if (measurementList.Count > 0)
					return measurementList[0];
				else
					return null;
			}
			catch (Exception ex)
			{
				LogException("GetMeasurementsForOutputStream", ex);
				return null;
			}
		}

		#endregion
		
		#region " Manage Contacts Code"

		public static List<Contact> GetContactList(int companyID)
		{			
			DataConnection connection = new DataConnection();
			try
			{
				List<Contact> contactList = new List<Contact>();
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "SELECT * FROM ContactDetail WHERE CompanyID = @companyID ORDER BY ContactTypeID, FirstName, LastName";
				command.Parameters.Add(AddWithValue(command, "@companyID", companyID));
				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				contactList = (from item in resultTable.AsEnumerable()
							   select new Contact()
							   {
								   ID = item.Field<int>("ID"),
								   CompanyID = item.Field<int>("CompanyID"),
								   ContactTypeID = item.Field<int>("ContactTypeID"),
								   FirstName = item.Field<string>("FirstName"),
								   LastName = item.Field<string>("LastName"),
								   Title = item.Field<string>("Title"),
								   Email = item.Field<string>("Email"),
								   Phone = item.Field<string>("Phone"),
								   CompanyName = item.Field<string>("CompanyName"),
								   ContactTypeName = item.Field<string>("ContactTypeName")
							   }).ToList();
				return contactList;
			}
			catch (Exception ex)
			{
				LogException("GetContactList", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Contact List", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}
		}

		public static string SaveContact(Contact contact, bool isNew)
		{
			DataConnection connection = new DataConnection();
			try
			{
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;

				if (isNew)
					command.CommandText = "INSERT INTO Contact (CompanyID, ContactTypeID, FirstName, LastName, Title, Email, Phone) " +
						"VALUES (@companyID, @contactTypeID, @firstName, @lastName, @title, @email, @phone)";
				else
					command.CommandText = "UPDATE Contact SET CompanyID = @companyID, ContactTypeID = @contactTypeID, FirstName = @firstName, " +
						"LastName = @lastName, Title = @title, Email = @email, Phone = @phone WHERE ID = @id";

				command.Parameters.Add(AddWithValue(command, "@companyID", contact.CompanyID));
				command.Parameters.Add(AddWithValue(command, "@contactTypeID", contact.ContactTypeID));
				command.Parameters.Add(AddWithValue(command, "@firstName", contact.FirstName));
				command.Parameters.Add(AddWithValue(command, "@lastName", contact.LastName));
				command.Parameters.Add(AddWithValue(command, "@title", contact.Title));
				command.Parameters.Add(AddWithValue(command, "@email", contact.Email));
				command.Parameters.Add(AddWithValue(command, "@phone", contact.Phone));
				
				if (!isNew)
					command.Parameters.Add(AddWithValue(command, "@id", contact.ID));

				command.ExecuteNonQuery();
				return "Contact Information Saved Successfully";
			}
			catch (Exception ex)
			{
				LogException("SaveContact", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Save Contact Information", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);				
			}
			finally
			{
				connection.Dispose();
			}
		}

		public static Dictionary<int, string> GetTechnicalContactByCompany(int companyID, bool isOptional)
		{
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<int, string> contactList = new Dictionary<int, string>();
				if (isOptional)
					contactList.Add(0, "Select Technical Contact");				
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "SELECT ID, ContactFullName FROM ContactDetail WHERE CompanyID = @companyID AND ContactTypeName = @contactTypeName ORDER BY FirstName, LastName";
				command.Parameters.Add(AddWithValue(command, "@companyID", companyID));
				command.Parameters.Add(AddWithValue(command, "@contactTypeName", "Technical"));
				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!contactList.ContainsKey(Convert.ToInt32(row["ID"])))
						contactList.Add(Convert.ToInt32(row["ID"]), row["ContactFullName"].ToString());
				}
				return contactList;
			}
			catch (Exception ex)
			{
				LogException("GetTechnicalContactByCompany", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Technical Contact By Company", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}						
		}

		#endregion

		#region " Manage Companies Code"

		public static List<Company> GetCompanyList()
		{			
			DataConnection connection = new DataConnection();
			try
			{
				List<Company> companyList = new List<Company>();
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "SELECT * FROM CompanyDetail ORDER BY Acronym";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				companyList = (from item in resultTable.AsEnumerable()
							   select new Company()
							   {
								   ID = item.Field<int>("ID"),
								   Acronym = item.Field<string>("Acronym"),
								   MapAcronym = item.Field<string>("MapAcronym"),
								   Name = item.Field<string>("Name"),
								   URL = item.Field<string>("URL"),
								   InterconnectionID = item.Field<int>("InterconnectionID"),
								   NercRegionID = item.Field<int>("NercRegionID"),
								   TimeZone = item.Field<string>("TimeZone"),
								   InterconnectionName = item.Field<string>("InterconnectionName"),
								   NercRegionName = item.Field<string>("NercRegionName")
							   }).ToList();
				return companyList;
			}
			catch (Exception ex)
			{
				LogException("GetCompanyList", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Company List", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}						
		}

		public static Dictionary<int, string> GetCompanies(bool isOptional)
		{			
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<int, string> companyList = new Dictionary<int, string>();
				if (isOptional)
					companyList.Add(0, "Select Company");
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "SELECT ID, Name FROM Company ORDER BY Name";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				int id;
				foreach (DataRow row in resultTable.Rows)
				{
					id = int.Parse(row["ID"].ToString());

					if (!companyList.ContainsKey(id))
						companyList.Add(id, row["Name"].ToString());
				}
				return companyList;
			}
			catch (Exception ex)
			{
				LogException("GetCompanies", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Companies", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}			
			
		}

		public static string SaveCompany(Company company, bool isNew)
		{
			DataConnection connection = new DataConnection();
			try
			{
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;

				if (isNew)
					command.CommandText = "INSERT INTO Company (Acronym, MapAcronym, Name, URL, InterconnectionID, NercRegionID, TimeZone) " +
						"VALUES (@acronym, @mapAcronym, @name, @url, @interconnectionID, @nercRegionID, @timeZone)";
				else
					command.CommandText = "UPDATE Company SET Acronym = @acronym, MapAcronym = @mapAcronym, Name = @name, URL = @url, " + 
						"InterconnectionID = @interconnectionID, NercRegionID = @nercRegionID, TimeZone = @timeZone WHERE ID = @id";

				command.Parameters.Add(AddWithValue(command, "@acronym", company.Acronym));
				command.Parameters.Add(AddWithValue(command, "@mapAcronym", company.MapAcronym));
				command.Parameters.Add(AddWithValue(command, "@name", company.Name));
				command.Parameters.Add(AddWithValue(command, "@url", company.URL));
				command.Parameters.Add(AddWithValue(command, "@interconnectionID", company.InterconnectionID));
				command.Parameters.Add(AddWithValue(command, "@nercRegionID", company.NercRegionID));
				command.Parameters.Add(AddWithValue(command, "@timeZone", company.TimeZone));

				if (!isNew)
					command.Parameters.Add(AddWithValue(command, "@id", company.ID));

				command.ExecuteNonQuery();
				return "Company Information Saved Successfully";
			}
			catch (Exception ex)
			{
				LogException("SaveCompany", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Save Company Information", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);				
			}
			finally
			{
				connection.Dispose();
			}
		}

		#endregion

		#region " Manage Vendors Code"

		public static List<Vendor> GetVendorList()
		{			
			DataConnection connection = new DataConnection();
			try
			{
				List<Vendor> vendorList = new List<Vendor>();
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select * FROM VendorDetail Order By Name";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());
				vendorList = (from item in resultTable.AsEnumerable()
							  select new Vendor()
							  {
								  ID = item.Field<int>("ID"),
								  Acronym = item.Field<string>("Acronym"),
								  Name = item.Field<string>("Name"),
								  PhoneNumber = item.Field<string>("PhoneNumber"),
								  ContactEmail = item.Field<string>("ContactEmail"),
								  URL = item.Field<string>("URL")
							  }).ToList();
				return vendorList;
			}
			catch (Exception ex)
			{
				LogException("GetVendorList", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Vendor List", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}						
		}

		public static Dictionary<int, string> GetVendors(bool isOptional)
		{			
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<int, string> vendorList = new Dictionary<int, string>();
				if (isOptional)
					vendorList.Add(0, "Select Vendor");
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select ID, Name From Vendor Order By Name";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!vendorList.ContainsKey(Convert.ToInt32(row["ID"])))
						vendorList.Add(Convert.ToInt32(row["ID"]), row["Name"].ToString());
				}
				return vendorList;
			}
			catch (Exception ex)
			{
				LogException("GetVendors", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Vendors", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}						
		}

		public static string SaveVendor(Vendor vendor, bool isNew)
		{
			DataConnection connection = new DataConnection();
			try
			{
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				if (isNew)
					command.CommandText = "Insert Into Vendor (Acronym, Name, PhoneNumber, ContactEmail, URL) Values (@acronym, @name, @phoneNumber, @contactEmail, @url)";
				else
					command.CommandText = "Update Vendor Set Acronym = @acronym, Name = @name, PhoneNumber = @phoneNumber, ContactEmail = @contactEmail, URL = @url Where ID = @id";

				command.Parameters.Add(AddWithValue(command, "@acronym", vendor.Acronym));
				command.Parameters.Add(AddWithValue(command, "@name", vendor.Name));
				command.Parameters.Add(AddWithValue(command, "@phoneNumber", vendor.PhoneNumber));
				command.Parameters.Add(AddWithValue(command, "@contactEmail", vendor.ContactEmail));
				command.Parameters.Add(AddWithValue(command, "@url", vendor.URL));

				if (!isNew)
					command.Parameters.Add(AddWithValue(command, "@id", vendor.ID));

				command.ExecuteNonQuery();
				return "Vendor Information Saved Successfully";
			}
			catch (Exception ex)
			{
				LogException("SaveVendor", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Save Vendor Information", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);				
			}
			finally
			{
				connection.Dispose();
			}
		}

		#endregion

		#region " Manage Vendor Devices Code"

		public static List<VendorDevice> GetVendorDeviceList()
		{			
			DataConnection connection = new DataConnection();
			try
			{
				List<VendorDevice> vendorDeviceList = new List<VendorDevice>();
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select * From VendorDeviceDetail Order By Name";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());
				vendorDeviceList = (from item in resultTable.AsEnumerable()
									select new VendorDevice()
									{
										ID = item.Field<int>("ID"),
										VendorID = item.Field<int>("VendorID"),
										Name = item.Field<string>("Name"),
										Description = item.Field<string>("Description"),
										URL = item.Field<string>("URL"),
										VendorName = item.Field<string>("VendorName")
									}).ToList();
				return vendorDeviceList;
			}
			catch (Exception ex)
			{
				LogException("GetVendorDeviceList", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Vendor Device List", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}						
		}

		public static string SaveVendorDevice(VendorDevice vendorDevice, bool isNew)
		{
			DataConnection connection = new DataConnection();
			try
			{
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				if (isNew)
					command.CommandText = "Insert Into VendorDevice (VendorID, Name, Description, URL) Values (@vendorID, @name, @description, @url)";
				else
					command.CommandText = "Update VendorDevice Set VendorID = @vendorID, Name = @name, Description = @description, URL = @url Where ID = @id";

				command.Parameters.Add(AddWithValue(command, "@vendorID", vendorDevice.VendorID));
				command.Parameters.Add(AddWithValue(command, "@name", vendorDevice.Name));
				command.Parameters.Add(AddWithValue(command, "@description", vendorDevice.Description));
				command.Parameters.Add(AddWithValue(command, "@url", vendorDevice.URL));

				if (!isNew)
					command.Parameters.Add(AddWithValue(command, "@id", vendorDevice.ID));

				command.ExecuteNonQuery();

				return "Vendor Device Information Saved Successfully";
			}
			catch (Exception ex)
			{
				LogException("SaveVendorDevice", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Save Vendor Device Information", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);				
			}
			finally
			{
				connection.Dispose();
			}
		}

		public static Dictionary<int, string> GetVendorDevices(bool isOptional)
		{			
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<int, string> vendorDeviceList = new Dictionary<int, string>();
				if (isOptional)
					vendorDeviceList.Add(0, "Select Vendor Device");
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select ID, Name From VendorDevice Order By Name";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!vendorDeviceList.ContainsKey(Convert.ToInt32(row["ID"])))
						vendorDeviceList.Add(Convert.ToInt32(row["ID"]), row["Name"].ToString());
				}
				return vendorDeviceList;
			}
			catch (Exception ex)
			{
				LogException("GetVendorDevices", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Vendor Devices", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}						
		}

		#endregion

		#region " Manage Network Models"

		public static List<NetworkModel> GetNetworkModelList()
		{
			DataConnection connection = new DataConnection();
			try
			{
				List<NetworkModel> networkModelList = new List<NetworkModel>();
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select * FROM NetworkModelDetail Order By NetworkElementUID";
				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());
				networkModelList = (from item in resultTable.AsEnumerable()
									select new NetworkModel()
									{
										ID = item.Field<int>("ID"),
										NetworkElementUID = item.Field<string>("NetworkElementUID"),
										LineBusName = item.Field<string>("LineBusName"),
										RegionalLineBusID = item.Field<string>("RegionalLineBusID"),
										LocalLineBusID = item.Field<string>("LocalLineBusID"),
										ExtraLineBusID =item.Field<string>("ExtraLineBusID"),
										NominalKv = item.Field<int?>("NominalKv")
									}).ToList();

				return networkModelList;
			}
			catch (Exception ex)
			{
				LogException("GetNetworkModelList", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Network Model List", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}	
		}

		public static Dictionary<int, string> GetNetworkModels(bool isOptional)
		{
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<int, string> networkModelList = new Dictionary<int, string>();
				if (isOptional)
					networkModelList.Add(0, "Select Network Model");
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select ID, NetworkElementUID From NetworkModel Order By NetworkElementUID";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!networkModelList.ContainsKey(Convert.ToInt32(row["ID"])))
						networkModelList.Add(Convert.ToInt32(row["ID"]), row["NetworkElementUID"].ToString());
				}
				return networkModelList;
			}
			catch (Exception ex)
			{
				LogException("GetNetworkModels", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Network Models", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}	
		}

		public static string SaveNetworkModel(NetworkModel model, bool isNew)
		{
			DataConnection connection = new DataConnection();
			try
			{
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				if (isNew)
					command.CommandText = "Insert Into NetworkModel (NetworkElementUID, LineBusName, RegionalLineBusID, LocalLineBusID, ExtraLineBusID, NominalKv) " +
						"Values (@networkElementUID, @lineBusName, @regionalLineBusID, @localLineBusID, @extraLineBusID, @nominalKv)";
				else
					command.CommandText = "Update NetworkModel Set NetworkElementUID = @networkElementUID, LineBusName = @lineBusName, RegionalLineBusID = @regionalLineBusID, " +
						"LocalLineBusID = @localLineBusID, ExtraLineBusID = @extraLineBusID, NominalKv = @nominalKv Where ID = @id";

				command.Parameters.Add(AddWithValue(command, "@networkElementUID", model.NetworkElementUID));
				command.Parameters.Add(AddWithValue(command, "@lineBusName", model.LineBusName));
				command.Parameters.Add(AddWithValue(command, "@regionalLineBusID", model.RegionalLineBusID));
				command.Parameters.Add(AddWithValue(command, "@localLineBusID", model.LocalLineBusID));
				command.Parameters.Add(AddWithValue(command, "@extraLineBusID", model.ExtraLineBusID));
				command.Parameters.Add(AddWithValue(command, "@nominalKv", model.NominalKv ?? (object)DBNull.Value));

				if (!isNew)
					command.Parameters.Add(AddWithValue(command, "@id", model.ID));

				command.ExecuteNonQuery();
				return "Network Model Information Saved Successfully";
			}
			catch (Exception ex)
			{
				LogException("SaveNetworkModel", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Save Network Model Information", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}
		}

		#endregion

		#region " Manage Interconnections Code"

		public static Dictionary<int, string> GetInterconnections(bool isOptional)
		{			
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<int, string> interconnectionList = new Dictionary<int, string>();
				if (isOptional)
					interconnectionList.Add(0, "Select Interconnection");
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select ID, Name From Interconnection Order By Name";

				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!interconnectionList.ContainsKey(Convert.ToInt32(row["ID"])))
						interconnectionList.Add(Convert.ToInt32(row["ID"]), row["Name"].ToString());
				}
				return interconnectionList;
			}
			catch (Exception ex)
			{
				LogException("GetInterconnections", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Interconnections", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}						
		}

		public static Dictionary<int, string> GetNercRegions(bool isOptional)
		{			
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<int, string> nercRegionList = new Dictionary<int, string>();			
				if (isOptional)
					nercRegionList.Add(0, "Select NERC Region");
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select ID, Name + ' - ' + Description AS RegionName From NercRegion Order By Name";
				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!nercRegionList.ContainsKey(Convert.ToInt32(row["ID"])))
						nercRegionList.Add(Convert.ToInt32(row["ID"]), row["RegionName"].ToString());
				}
				return nercRegionList;				
			}
			catch (Exception ex)
			{
				LogException("GetNercRegions", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve NERC Regions", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}
		}

		#endregion

		#region " Manage Protocols Code"

		public static Dictionary<int, string> GetProtocols(bool isOptional)
		{			
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<int, string> protocolList = new Dictionary<int, string>();			
				if (isOptional)
					protocolList.Add(0, "Select Protocol");
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select ID, Name From Protocol Order By Name";
				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!protocolList.ContainsKey(Convert.ToInt32(row["ID"])))
						protocolList.Add(Convert.ToInt32(row["ID"]), row["Name"].ToString());
				}
				return protocolList;
			}
			catch (Exception ex)
			{
				LogException("GetProtocols", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Protocols", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}						
		}

		#endregion

		#region " Manage Signal Types Code"

		public static Dictionary<int, string> GetSignalTypes(bool isOptional)
		{			
			DataConnection connection = new DataConnection();
			try
			{
				Dictionary<int, string> signalTypeList = new Dictionary<int, string>();
				if (isOptional)
					signalTypeList.Add(0, "Select Signal Type");
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select ID, Name From SignalType Order By Name";
				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				foreach (DataRow row in resultTable.Rows)
				{
					if (!signalTypeList.ContainsKey(Convert.ToInt32(row["ID"])))
						signalTypeList.Add(Convert.ToInt32(row["ID"]), row["Name"].ToString());
				}
				return signalTypeList;
			}
			catch (Exception ex)
			{
				LogException("GetSignalTypes", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve Signal Types", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}
		}

		static DataTable GetPmuSignalTypes()	//Do not use this method in WCF call or silverlight. It is for internal use only.
		{
			DataConnection connection = new DataConnection();
			IDbCommand command = connection.Connection.CreateCommand();
			command.CommandType = CommandType.Text;
			command.CommandText = "Select * From SignalType Where Source = 'PMU' AND Suffix IN ('FQ', 'DF', 'SF')";
			DataTable resultTable = new DataTable();
			resultTable.Load(command.ExecuteReader());
			return resultTable;
		}

		static DataTable GetPhasorSignalTypes(string phasorType)
		{
			DataConnection connection = new DataConnection();
			IDbCommand command = connection.Connection.CreateCommand();
			command.CommandType = CommandType.Text;
			if (phasorType == "V")
				command.CommandText = "Select * From SignalType Where Source = 'Phasor' AND Acronym LIKE 'V%'";
			else
				command.CommandText = "Select * From SignalType Where Source = 'Phasor' AND Acronym LIKE 'I%'";
			DataTable resultTable = new DataTable();
			resultTable.Load(command.ExecuteReader());
			return resultTable;
		}

		static int GetSignalTypeID(string suffix)
		{
			int signalTypeID = 0;
			DataConnection connection = new DataConnection();
			try
			{
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "Select ID From SignalType Where Suffix = @suffix";
				command.Parameters.Add(AddWithValue(command, "@suffix", suffix));
				signalTypeID = (int)command.ExecuteScalar();
			}
			catch (Exception ex)
			{
				LogException("GetSignalTypeID", ex);
			}
			finally
			{
				connection.Dispose();
			}
			return signalTypeID;
		}

		#endregion

		#region " Manage User Comments"

		public static List<UserComment> GetUserCommentList()
		{
			DataConnection connection = new DataConnection();
			try
			{
				List<UserComment> commentList = new List<UserComment>();
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "SELECT * FROM UserComment ORDER BY CreatedOn DESC";
				DataTable resultTable = new DataTable();
				resultTable.Load(command.ExecuteReader());

				commentList = (from item in resultTable.AsEnumerable()
							   select new UserComment()
							   {
								   ID = item.Field<int>("ID"),					
								   UserName = item.Field<string>("UserName"),
								   Subject = item.Field<string>("Subject"),
								   Comment = item.Field<string>("Comment"),
								   CreatedOn = item.Field<DateTime>("CreatedOn")								   
							   }).ToList();
				return commentList;
			}
			catch (Exception ex)
			{
				LogException("GetUserCommentList", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Retrieve User Comment List", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}
		}

		public static string SaveUserComment(UserComment comment)
		{
			DataConnection connection = new DataConnection();
			try
			{
				IDbCommand command = connection.Connection.CreateCommand();
				command.CommandType = CommandType.Text;
				command.CommandText = "INSERT INTO UserComment (UserName, Subject, Comment) VALUES (@userID, @subject, @comment)";
				command.Parameters.Add(AddWithValue(command, "@userID", comment.UserName));
				command.Parameters.Add(AddWithValue(command, "@subject", comment.Subject));
				command.Parameters.Add(AddWithValue(command, "@comment", comment.Comment));

				command.ExecuteNonQuery();
				return "User Comment Saved Successfully";
			}
			catch (Exception ex)
			{
				LogException("SaveUserComment", ex);
				CustomServiceFault fault = new CustomServiceFault() { UserMessage = "Failed to Save User Comment Information", SystemMessage = ex.Message };
				throw new FaultException<CustomServiceFault>(fault);
			}
			finally
			{
				connection.Dispose();
			}
		}

		#endregion

	}
}